/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package logic.mapping;

//import com.sun.xml.internal.bind.v2.runtime.reflect.Accessor.GetterOnlyReflection;
import entityBeans.Acceptednameusage;
import entityBeans.Associatedsequence;
import entityBeans.Basisofrecord;
import entityBeans.Behavior;
import entityBeans.Collectioncode;
import entityBeans.Dataset;
import entityBeans.Dynamicproperty;
import entityBeans.Eventelement;
import entityBeans.Georeferencedby;
import entityBeans.Georeferencesource;
import entityBeans.Geospatialelement;
import entityBeans.Genus;
import entityBeans.Identificationelement;
import entityBeans.Identifiedby;
import entityBeans.Individual;
import entityBeans.Institutioncode;
import entityBeans.Interaction;
import entityBeans.Kingdom;
import entityBeans.Localityelement;
import entityBeans.Occurrenceelement;
import entityBeans.Ownerinstitution;
import entityBeans.Phylum;
import entityBeans.Preparation;
import entityBeans.Recordedby;
import entityBeans.Recordlevelelement;
import entityBeans.Scientificname;
import entityBeans.Class;
import entityBeans.Continent;
import entityBeans.Country;
import entityBeans.County;
import entityBeans.Disposition;
import entityBeans.Establishmentmean;
import entityBeans.Family;
import entityBeans.Georeferenceverificationstatus;
import entityBeans.Habitat;
import entityBeans.Identificationqualifier;
import entityBeans.Infraspecificepithet;
import entityBeans.Interactiontype;
import entityBeans.Island;
import entityBeans.Islandgroup;
import entityBeans.Lifestage;
import entityBeans.Locality;
import entityBeans.Municipality;
import entityBeans.Nameaccordingto;
import entityBeans.Namepublishedin;
import entityBeans.Nomenclaturalcode;
import entityBeans.Order1;
import entityBeans.Originalnameusage;
import entityBeans.Parentnameusage;
import entityBeans.Reproductivecondition;
import entityBeans.Samplingprotocol;
import entityBeans.Scientificnameauthorship;
import entityBeans.Sex;
import entityBeans.Specificepithet;
import entityBeans.Specimen;
import entityBeans.Stateprovince;
import entityBeans.Subgenus;
import entityBeans.Taxonconcept;
import entityBeans.Taxonomicelement;
import entityBeans.Taxonomicstatus;
import entityBeans.Taxonrank;
import entityBeans.Type;
import entityBeans.Typestatus;
import entityBeans.Waterbody;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import xlsBeans.InteractionXLS;
import xlsBeans.OccurrenceElementsXLS;
import xlsBeans.SpecimenXLS;

/**
 *
 * @author Lucas
 */
public class XLSToEntity {

    public XLSToEntity() {
    }

    public Interaction mapping(InteractionXLS intxls) {
        Interaction interaction = new Interaction();
        interaction.setInteractiontype(new Interactiontype());
        interaction.setInteractionrelatedinformation(intxls.getInteractioninformation());
        interaction.getInteractiontype().setInteractiontype(intxls.getInteractiontype());
        return interaction;
    }

    public Date formatDate(String d) {
        try{
        int ano = Integer.parseInt(d.substring(0, 4));
        int mes = Integer.parseInt(d.substring(5, 7)) - 1;
        int dia = Integer.parseInt(d.substring(8, 10));
        Calendar c = Calendar.getInstance();
        c.set(ano, mes, dia);

        return c.getTime();
        } catch (Exception e){
            return null;
        }
    }

    public Date formatTime(String t, String d) {
        try{
        String[] time = t.split(":");
        int hora = Integer.parseInt(time[0]);
        int min = Integer.parseInt(time[1]);
        int seg = Integer.parseInt(time[2]);


        int ano = Integer.parseInt(d.substring(0, 4));
        int mes = Integer.parseInt(d.substring(5, 7)) - 1;
        int dia = Integer.parseInt(d.substring(8, 10));

        Calendar c = Calendar.getInstance();
        c.set(ano, mes, dia, hora, min, seg);

        return c.getTime();
        } catch (Exception e){
            return null;
        }
    }

    public Specimen mapping(SpecimenXLS xls) {

        if (xls.getMain().getInstitutioncode().equals("") || xls.getMain().getBasisofrecord().equals("") || xls.getMain().getCollectioncode().equals("")
                || xls.getMain().getScientificname().equals("") || xls.getMain().getCatalognumber().equals("")) {
            System.out.println("SSS ERROR: Main Fields must be completed.");
            return null;
        }


        Specimen sp = new Specimen();

        Recordlevelelement recordlevel = new Recordlevelelement();
        Occurrenceelement occurrence = new Occurrenceelement();
        Identificationelement identification = new Identificationelement();
        Eventelement event = new Eventelement();
        Localityelement locality = new Localityelement();
        Geospatialelement geo = new Geospatialelement();
        Taxonomicelement taxon = new Taxonomicelement();

        // Main fields
        Institutioncode institution = new Institutioncode(null, xls.getMain().getInstitutioncode().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Basisofrecord basis = new Basisofrecord(null, xls.getMain().getBasisofrecord().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Collectioncode collection = new Collectioncode(null, xls.getMain().getCollectioncode().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Scientificname scientificName = new Scientificname(null, xls.getMain().getScientificname().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''").replaceAll("\\s+", " "));

        recordlevel.setInstitutioncode(institution);
        recordlevel.setBasisofrecord(basis);
        recordlevel.setCollectioncode(collection);
        taxon.setScientificname(scientificName);

        occurrence.setCatalognumber(xls.getMain().getCatalognumber().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));

        // Record-level Elements
        Type type = new Type(null, xls.getRec().getType().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Ownerinstitution owner = new Ownerinstitution(null, xls.getRec().getOwnercode().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Dataset dataset = new Dataset(null, xls.getRec().getDataset().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));

        recordlevel.setType(type);
        recordlevel.setOwnerinstitution(owner);
        recordlevel.setDataset(dataset);

        recordlevel.setIsrestricted(xls.getPrivate1());
        recordlevel.setAccessrights(xls.getRec().getAccessrights().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        recordlevel.setRights(xls.getRec().getRights().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        recordlevel.setRightsholder(xls.getRec().getRightsholder().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        recordlevel.setInformationwithheld(xls.getRec().getInformationwithheld().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        recordlevel.setDatageneralization(xls.getRec().getDatageneralization().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));

        List<Dynamicproperty> dynamicList = new ArrayList<Dynamicproperty>();
        String[] dynamic = xls.getRec().getDynamicproperties().split(",");
        for (String aux : dynamic) {
            if (!aux.trim().equals("")) {
                Dynamicproperty d = new Dynamicproperty();
                d.setDynamicproperty(aux.trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
                dynamicList.add(d);
            }
        }
        recordlevel.setDynamicpropertyList(dynamicList);

        sp.setRecordlevelelement(recordlevel);

        // Taxonomic Elements
        Kingdom kingdom = new Kingdom(null, xls.getTax().getKingdom().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Phylum phylum = new Phylum(null, xls.getTax().getPhylum().trim());
        Class class1 = new Class(null, xls.getTax().getClasse().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Order1 order = new Order1(null, xls.getTax().getOrder().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Family family = new Family(null, xls.getTax().getFamily().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Genus genus = new Genus(null, xls.getTax().getGenus().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Subgenus subgenus = new Subgenus(null, xls.getTax().getSubgenus().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Specificepithet specificepithet = new Specificepithet(null, xls.getTax().getSpecificepithet().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Infraspecificepithet infraspecificepithet = new Infraspecificepithet(null, xls.getTax().getInfraspecificepithet().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Taxonrank taxonrank = new Taxonrank(null, xls.getTax().getTaxonrank().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Scientificnameauthorship scientificnameauthorship = new Scientificnameauthorship(null, xls.getTax().getScientificnameauthor().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Nomenclaturalcode nomenclaturalcode = new Nomenclaturalcode(null, xls.getTax().getNomenclaturalcode().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Taxonconcept taxonconcept = new Taxonconcept(null, xls.getTax().getTaxonconcept().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Acceptednameusage acceptednameusage = new Acceptednameusage(null, xls.getTax().getAcceptednameusage().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Parentnameusage parentnameusage = new Parentnameusage(null, xls.getTax().getAcceptednameusage().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Originalnameusage originalnameusage = new Originalnameusage(null, xls.getTax().getOriginalnameusage().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Nameaccordingto nameaccordingto = new Nameaccordingto(null, xls.getTax().getNameaccordingto().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Namepublishedin namepublishedin = new Namepublishedin(null, xls.getTax().getNamepublishedin().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Taxonomicstatus taxonomicstatus = new Taxonomicstatus(null, xls.getTax().getTaxonomicstatus().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));

        taxon.setKingdom(kingdom);
        taxon.setPhylum(phylum);
        taxon.setClass1(class1);
        taxon.setOrder1(order);
        taxon.setFamily(family);
        taxon.setGenus(genus);
        taxon.setSubgenus(subgenus);
        taxon.setSpecificepithet(specificepithet);
        taxon.setInfraspecificepithet(infraspecificepithet);
        taxon.setTaxonrank(taxonrank);
        taxon.setScientificnameauthorship(scientificnameauthorship);
        taxon.setNomenclaturalcode(nomenclaturalcode);
        taxon.setTaxonconcept(taxonconcept);
        taxon.setAcceptednameusage(acceptednameusage);
        taxon.setParentnameusage(parentnameusage);
        taxon.setOriginalnameusage(originalnameusage);
        taxon.setNameaccordingto(nameaccordingto);
        taxon.setNamepublishedin(namepublishedin);
        taxon.setTaxonomicstatus(taxonomicstatus);

        taxon.setNomenclaturalstatus(xls.getTax().getNomenclaturalstatus().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        taxon.setVernacularname(xls.getTax().getVernacularname().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        taxon.setVerbatimtaxonrank(xls.getTax().getVerbatimtaxonrank().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        taxon.setTaxonremark(xls.getTax().getTaxonremarks().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));

        sp.setTaxonomicelement(taxon);

        //Occurence Elements
        Sex sex = new Sex(null, xls.getOcc().getSex().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Behavior behavior = new Behavior(null, xls.getOcc().getBehavior().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Lifestage lifestage = new Lifestage(null, xls.getOcc().getLifestage().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Disposition disposition = new Disposition(null, xls.getOcc().getDisposition().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Reproductivecondition reproductivecondition = new Reproductivecondition(null, xls.getOcc().getReproductivecondition().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Establishmentmean establishmentmean = new Establishmentmean(null, xls.getOcc().getEstabilishmentmean().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));

        occurrence.setSex(sex);
        occurrence.setBehavior(behavior);
        occurrence.setLifestage(lifestage);
        occurrence.setDisposition(disposition);
        occurrence.setReproductivecondition(reproductivecondition);
        occurrence.setEstablishmentmean(establishmentmean);

        if (xls.getOcc().getIndividualcount() != null) {
            occurrence.setIndividualcount(xls.getOcc().getIndividualcount().intValue());
        } else {
            occurrence.setIndividualcount(null);
        }
        occurrence.setRecordnumber(xls.getOcc().getRecordnumber().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        occurrence.setOthercatalognumber(xls.getOcc().getOthercatalognumbers().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        occurrence.setOccurrencedetail(xls.getOcc().getOccurrencedetails().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        occurrence.setOccurrenceremark(xls.getOcc().getOccurrenceremarks().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        occurrence.setOccurrencestatus(xls.getOcc().getOccurrencestatus().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));

        List<Individual> individualList = new ArrayList<Individual>();
        String[] individual = xls.getOcc().getIndividual().split(",");
        for (String aux : individual) {
            if (!aux.trim().equals("")) {
                Individual i = new Individual();
                i.setIndividual(aux.trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
                individualList.add(i);
            }
        }
        occurrence.setIndividualList(individualList);

        List<Recordedby> recordedList = new ArrayList<Recordedby>();
        String[] recordedby = xls.getOcc().getRecordedby().split(",");
        for (String aux : recordedby) {
            if (!aux.trim().equals("")) {
                Recordedby r = new Recordedby();
                r.setRecordedby(aux.trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
                recordedList.add(r);
            }
        }
        occurrence.setRecordedbyList(recordedList);

        List<Preparation> preparationList = new ArrayList<Preparation>();
        String[] preparation = xls.getOcc().getPreparations().split(",");
        for (String aux : preparation) {
            if (!aux.trim().equals("")) {
                Preparation p = new Preparation();
                p.setPreparation(aux.trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
                preparationList.add(p);
            }
        }
        occurrence.setPreparationList(preparationList);

        List<Associatedsequence> associatedList = new ArrayList<Associatedsequence>();
        String[] associated = xls.getOcc().getAssociatedsequences().split(",");
        for (String aux : associated) {
            if (!aux.trim().equals("")) {
                Associatedsequence a = new Associatedsequence();
                a.setAssociatedsequence(aux.trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
                associatedList.add(a);
            }
        }
        occurrence.setAssociatedsequenceList(associatedList);

        sp.setOccurrenceelement(occurrence);

        //Identification Elements
        Identificationqualifier identificationqualifier = new Identificationqualifier(null, xls.getId().getIdentificationqualifier().trim());
        identification.setIdentificationqualifier(identificationqualifier);
        identification.setDateidentified(xls.getId().getDateidentified().equals("") ? null : formatDate(xls.getId().getDateidentified()));
        identification.setIdentificationremark(xls.getId().getIdentificationremarks());

        List<Identifiedby> identifiedList = new ArrayList<Identifiedby>();
        String[] identified = xls.getId().getIdentifiedby().split(",");
        for (String aux : identified) {
            if (!aux.trim().equals("")) {
                Identifiedby i = new Identifiedby();
                i.setIdentifiedby(aux.trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
                identifiedList.add(i);
            }
        }
        identification.setIdentifiedbyList(identifiedList);

        List<Typestatus> typestatusList = new ArrayList<Typestatus>();
        String[] typestatus = xls.getId().getTypestatus().split(",");
        for (String aux : typestatus) {
            if (!aux.trim().equals("")) {
                Typestatus t = new Typestatus();
                t.setTypestatus(aux.trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
                typestatusList.add(t);
            }
        }
        identification.setTypestatusList(typestatusList);

        sp.setIdentificationelement(identification);

        //Event Elements
        Samplingprotocol samplingprotocol = new Samplingprotocol(null, xls.getEve().getSamplingprotocol().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        Habitat habitat = new Habitat(null, xls.getEve().getHabitat().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));

        event.setSamplingprotocol(samplingprotocol);
        event.setHabitat(habitat);

        event.setSamplingeffort(xls.getEve().getSamplingeffort().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        event.setVerbatimeventdate(xls.getEve().getVerbatimeventdate().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        event.setEventtime(xls.getEve().getEventtime().equals("") ? null : formatTime(xls.getEve().getEventtime(), xls.getEve().getEventdate()));
        event.setEventdate(xls.getEve().getEventdate().equals("") ? null : formatDate(xls.getEve().getEventdate()));
        event.setFieldnumber(xls.getEve().getFieldnumber().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        event.setFieldnote(xls.getEve().getFieldnotes().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        event.setEventremark(xls.getEve().getEventremarks().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));

        sp.setEventelement(event);

        //Locality Elements

        Continent continent = new Continent(null, xls.getLoc().getContinent().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''").replaceAll("\\s+", " "));
        Country country = new Country(null, xls.getLoc().getCountry().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''").replaceAll("\\s+", " "));
        Stateprovince stateprovince = new Stateprovince(null, xls.getLoc().getStateorprovince().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''").replaceAll("\\s+", " "));
        County county = new County(null, xls.getLoc().getCounty().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''").replaceAll("\\s+", " "));
        Municipality municipality = new Municipality(null, xls.getLoc().getMunicipality().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''").replaceAll("\\s+", " "));
        Locality locality1 = new Locality(null, xls.getLoc().getLocality().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''").replaceAll("\\s+", " "));
        Waterbody waterbody = new Waterbody(null, xls.getLoc().getWaterbody().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''").replaceAll("\\s+", " "));
        Islandgroup islandgroup = new Islandgroup(null, xls.getLoc().getIslandgroup().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''").replaceAll("\\s+", " "));
        Island island = new Island(null, xls.getLoc().getIsland().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''").replaceAll("\\s+", " "));

        locality.setContinent(continent);
        locality.setCountry(country);
        locality.setStateprovince(stateprovince);
        locality.setCounty(county);
        locality.setMunicipality(municipality);
        locality.setLocality(locality1);
        locality.setWaterbody(waterbody);
        locality.setIslandgroup(islandgroup);
        locality.setIsland(island);

        locality.setLocationaccordingto(xls.getLoc().getLocationaccordingto().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        locality.setCoordinateprecision(xls.getLoc().getCoordinateprecision().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        locality.setLocationremark(xls.getLoc().getLocationremarks().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        locality.setMinimumelevationinmeters(xls.getLoc().getMinimunelevation().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        locality.setMaximumelevationinmeters(xls.getLoc().getMaximumelevation().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        locality.setMinimumdepthinmeters(xls.getLoc().getMinimumdepth().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        locality.setMaximumdepthinmeters(xls.getLoc().getMaximumdepth().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        if (xls.getLoc().getMinimumdistance() != null) {
            locality.setMinimumdistanceabovesurfaceinmeters(xls.getLoc().getMinimumdistance().floatValue());
        } else {
            locality.setMinimumdistanceabovesurfaceinmeters(null);
        }
        if (xls.getLoc().getMaximumdistance() != null) {
            locality.setMaximumdistanceabovesurfaceinmeters(xls.getLoc().getMaximumdistance().floatValue());
        } else {
            locality.setMaximumdistanceabovesurfaceinmeters(null);
        }
        locality.setVerbatimdepth(xls.getLoc().getVerbatimdepth().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        locality.setVerbatimelevation(xls.getLoc().getVerbatimelevation().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        locality.setVerbatimlocality(xls.getLoc().getVerbatimlocality().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        locality.setVerbatimsrs(xls.getLoc().getVerbatimsrs().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        locality.setFootprintsrs(xls.getLoc().getFootprintsrs().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));

        List<Georeferencedby> georeferencedList = new ArrayList<Georeferencedby>();
        String[] georeferenced = xls.getLoc().getGeoreferencedby().split(",");
        for (String aux : georeferenced) {
            if (!aux.trim().equals("")) {
                Georeferencedby g = new Georeferencedby();
                g.setGeoreferencedby(aux.trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
                georeferencedList.add(g);
            }
        }
        locality.setGeoreferencedbyList(georeferencedList);

        List<Georeferencesource> georeferencesourceList = new ArrayList<Georeferencesource>();
        String[] georeferencesource = xls.getLoc().getGeoreferencesources().split(",");
        for (String aux : georeferencesource) {
            if (!aux.trim().equals("")) {
                Georeferencesource gs = new Georeferencesource();
                gs.setGeoreferencesource(aux.trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
                georeferencesourceList.add(gs);
            }
        }
        locality.setGeoreferencesourceList(georeferencesourceList);

        sp.setLocalityelement(locality);

        //Geospatial Elements

        Georeferenceverificationstatus georeferenceverificationstatus = new Georeferenceverificationstatus(null, xls.getGeo().getVerificationstatus().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));

        geo.setGeoreferenceverificationstatus(georeferenceverificationstatus);

        geo.setDecimallatitude(xls.getGeo().getDecimalaltitude());//double
        geo.setDecimallongitude(xls.getGeo().getDecimallongitude());
        geo.setCoordinateuncertaintyinmeters(xls.getGeo().getCoordinateuncertainty());
        geo.setGeodeticdatum(xls.getGeo().getGeodeticdatum().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        geo.setPointradiusspatialfit(xls.getGeo().getPointradiusspatialfit().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        geo.setVerbatimcoordinate(xls.getGeo().getVerbatimcoordinates().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        geo.setVerbatimlatitude(xls.getGeo().getVerbatimlatitude().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        geo.setVerbatimlongitude(xls.getGeo().getVerbatimlongitude().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        geo.setVerbatimcoordinatesystem(xls.getGeo().getVerbatimcoordinatesystem().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        geo.setGeoreferenceprotocol(xls.getGeo().getGeoreferenceprotocol().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        geo.getGeoreferenceverificationstatus().setGeoreferenceverificationstatus(xls.getGeo().getVerificationstatus().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        geo.setGeoreferenceremark(xls.getGeo().getGeoreferenceremarks().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        geo.setFootprintwkt(xls.getGeo().getFootprintwkt().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));
        geo.setFootprintspatialfit(xls.getGeo().getFootprintspatialfit().trim().replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "''"));

        sp.setGeospatialelement(geo);

        return sp;
    }
}
